<#----------------------------------------------------------------------------------
        NAME: Upsert-Storage.ps1
       EMAIL: saleyoun@hotmail.com
 DESCRIPTION: This script inserts a record in the Storage table in the iSQLPS system.
              If the record already exists, then this script updates the existing record.
------------------------------------------------------------------------------------
  DISCLAIMER: The AUTHOR ASSUMES NO RESPONSIBILITY FOR ANYTHING, including the 
  destruction of personal property, creating singularities, making deep fried chicken,
  causing your toilet to explode, making your animals spin around like mad, causing 
  hair loss, killing your buzz or ANYTHING else that can be thought up.
------------------------------------------------------------------------------------#>

[string] $strUpsertSql = ""
[string] $strQuerySql = ""

$strQuerySql = "SELECT ServerOSID, hostName FROM dbo.ServerOS;"
$sqlServers = Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $iSQLPSServer -Database $iSQLPSDatabase

foreach ($sqlServer in $sqlServers) {
  $sqlHostName = $sqlServer.hostName
  $sqlHostID = $sqlServer.ServerOSID

  $sqlStorage = Get-WmiObject -computername "$sqlHostName" Win32_LogicalDisk -filter "DriveType=3" | foreach{
		add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))
		add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))
		add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))
		add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru}
		  
  foreach ($sqldrive in $sqlStorage){
    $strUpsertSql = "EXEC UpsertStorage "+ $sqlHostID +", '"+ $sqldrive.DeviceID +"', '"+ $sqldrive.VolumeName +"', "+ $sqldrive.SizeGB +", "+ $sqldrive.FreeGB +", "+ $sqldrive.PercentFree
    $strUpsertSql
    Invoke-Sqlcmd -Query $strUpsertSql -ServerInstance $iSQLPSServer -Database $iSQLPSDatabase
  }
}
